﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using RM.Common;
using RM.DBUtility;
namespace RM.DAL
{
    /// <summary>
    /// 数据库访问类：管理角色
    /// </summary>
    public partial class RMRole
    {
        public RMRole()
        { }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from X_Role");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 返回角色名称
        /// </summary>
        public string GetRoleName(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 role_name from X_Role");
            strSql.Append(" where id=" + id);
            string rolename = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(rolename))
            {
                return "";
            }
            return rolename;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">对象实体</param>
        /// <returns></returns>
        public int Add(Model.RMRole model)
        {
            using (SqlConnection Conn = new SqlConnection(PubConstant.ConnectionString))
            {
                Conn.Open();
                using (SqlTransaction trans = Conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into X_Role (");
                        strSql.Append("name,remark)");
                        strSql.Append(" values(");
                        strSql.Append("@name,@remark)");
                        strSql.Append(";select @@IDENTITY");
                        SqlParameter[] parameters = {
                                                        new SqlParameter("@name",SqlDbType.VarChar,50),
                                                        new SqlParameter("@remark",SqlDbType.VarChar,200)};
                        object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                        model.Id = Convert.ToInt32(obj);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return 0;
                    }
                }
            }
            return model.Id;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(Model.RMRole model)
        {
            using (SqlConnection Conn = new SqlConnection(PubConstant.ConnectionString))
            {
                Conn.Open();
                using (SqlTransaction trans = Conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update X_Role set ");
                        strSql.Append("name=@name,");
                        strSql.Append("remark=@remark");
                        strSql.Append(" where id=@id");
                        SqlParameter[] parameters ={
                                                  new SqlParameter("@name",SqlDbType.VarChar,50),
                                                  new SqlParameter("@remark",SqlDbType.VarChar,200),
                                                  new SqlParameter("@id",SqlDbType.Int)};
                        parameters[0].Value = model.Name;
                        parameters[1].Value = model.Remark;
                        parameters[2].Value = model.Id;
                        DbHelperSQL.ExecuteSql(Conn, trans, strSql.ToString(), parameters);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 删除一条数据
        /// 删除角色的同时，将X_RoleModule（角色权限）里面的相应权限删除。
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            List<CommandInfo> sqlList = new List<CommandInfo>();
            //删除角色权限
            StringBuilder strSqlDelRoleModule = new StringBuilder();
            strSqlDelRoleModule.Append("delete from X_RoleModule");
            strSqlDelRoleModule.Append(" where roleid=@roleid");
            SqlParameter[] parameters0 ={
                                           new SqlParameter("roleid",SqlDbType.Int)};
            parameters0[0].Value = id;
            CommandInfo cmd = new CommandInfo(strSqlDelRoleModule.ToString(), parameters0);
            sqlList.Add(cmd);
            //删除角色
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from X_Role");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters ={
                                          new SqlParameter("@id",SqlDbType.Int)};
            parameters[0].Value = id;
            cmd = new CommandInfo(strSql.ToString(), parameters);
            sqlList.Add(cmd);
            int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqlList);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <param name="RoleIdList"></param>
        /// <returns></returns>
        public bool DeleteList(string RoleIdList)
        {
            bool iExecute = false;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from X_Role");
                strSql.Append(" where id in (" + RoleIdList + ") ");
                StringBuilder sb = new StringBuilder();
                sb.Append("delete from X_RoleModule");
                sb.Append(" where roleid in (" + RoleIdList + ")");
                int delrows = DbHelperSQL.ExecuteSql(sb.ToString());
                int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
                if (rows > 0)
                {
                    return true;
                }
            }
            catch
            {                
                return false;
            }
            return iExecute;
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.RMRole GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,name,remark from X_Role");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id",SqlDbType.Int)};
            parameters[0].Value = id;
            Model.RMRole model= new Model.RMRole();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.Id=int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                model.Name = ds.Tables[0].Rows[0]["name"].ToString();
                model.Remark = ds.Tables[0].Rows[0]["remark"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }        
        /// <summary>
        /// 获得一个非泛型集合的ArrayList
        /// </summary>
        /// <returns></returns>
        public List<Model.RMRole> GetAllModel()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,name,remark from X_Role");            
            //DataSet ds = DbHelperSQL.Query(strSql.ToString());
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            List<Model.RMRole> list = new List<Model.RMRole>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    Model.RMRole model = new Model.RMRole();
                    model.Id = int.Parse(row["id"].ToString());
                    model.Name = row["name"].ToString();
                    model.Remark = row["remark"].ToString();
                    list.Add(model);
                }
                return list;
            }
            else
            {
                return null;
            }            
        }
        /// <summary>
        /// 得到查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string fieldOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from X_Role");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), fieldOrder));
        }
    }
}
